
//SET DIRECTORY FIRST
*cd " "
import excel Vincent&Mango_RawData_File7of7.xls, sheet("Raw Expenditure Weighted Data") firstrow clear
drop if iso3c == "AUS" | iso3c == "ISR" | iso3c == "JPN"

gen amount_usr = ( w_amount / ppp ) / ( dob / 100 ) // converting expenditures to 2015 USD PPP 
gen amount_pc_usr = amount_usr / ( pop / 1000 ) // per capita ( million / million )
replace amount_pc_usr = amount_pc_usr / 100 // reinterpretation: 1 unit is $ 100 

keep iso3c year cofog sector amount_pc_usr 
rename iso3c ccode

drop if sector == "GS1314" // social security sector is negligible 

//sum local + state governments
replace sector = "GS1312" if sector == "GS1313"
collapse (sum) amount_pc_usr , by ( ccode year cofog sector )

//generate central government sector for AUT and DEU
bysort ccode year cofog : egen gg_b = sum (amount_pc_usr) if sector == "GS13"
bysort ccode year cofog : egen gg = mean (gg_b)
bysort ccode year cofog : egen sng_b = sum (amount_pc_usr) if sector == "GS1312"
bysort ccode year cofog : egen sng = mean (sng_b)
replace amount_pc_usr = gg - sng if sector == "GS13"
replace amount_pc_usr = gg if sector == "GS13" & sng == .
replace sector = "GS1311" if sector == "GS13"
drop gg* sng*

reshape wide amount_pc_usr , i(ccode year cofog) j(sector) string
reshape wide amount_pc_usrGS1311 amount_pc_usrGS1312 , i(ccode year) j(cofog) string

rename amount_pc_usrGS1311GF0402 cg_402 // Agriculture
rename amount_pc_usrGS1312GF0402 sng_402 // Agriculture
rename amount_pc_usrGS1311GF0403 cg_403 // Fuel and Energy
rename amount_pc_usrGS1312GF0403 sng_403 // Fuel and Energy 
rename amount_pc_usrGS1311GF0405 cg_405 // Transport
rename amount_pc_usrGS1312GF0405 sng_405 // Transport

rename amount_pc_usrGS1311GF0501 cg_501 // Waste Management
rename amount_pc_usrGS1312GF0501 sng_501 // Waste Management
rename amount_pc_usrGS1311GF0502 cg_502 // Wastewater
rename amount_pc_usrGS1312GF0502 sng_502 // Wastewater
rename amount_pc_usrGS1311GF0503 cg_503 // Pollution Abatement
rename amount_pc_usrGS1312GF0503 sng_503 // Pollution Abatement
rename amount_pc_usrGS1311GF0504 cg_504 // Protection of Biodiversity 
rename amount_pc_usrGS1312GF0504 sng_504 // Protection of Biodiversity 
rename amount_pc_usrGS1311GF0505 cg_505 // R&D Environment
rename amount_pc_usrGS1312GF0505 sng_505 // R&D Environment
rename amount_pc_usrGS1311GF0506 cg_506 // Environment other
rename amount_pc_usrGS1312GF0506 sng_506 // Environment other

rename amount_pc_usrGS1311GF0601 cg_601 // Housing Development
rename amount_pc_usrGS1312GF0601 sng_601 // Housing Development
rename amount_pc_usrGS1311GF0602 cg_602 // Community Development
rename amount_pc_usrGS1312GF0602 sng_602 // Community Development
rename amount_pc_usrGS1311GF0603 cg_603 // Water Supply
rename amount_pc_usrGS1312GF0603 sng_603 // Water Supply
rename amount_pc_usrGS1311GF0604 cg_604 // Street Lighting
rename amount_pc_usrGS1312GF0604 sng_604 // Street Lighting

order ccode year sng* cg*
format sng* cg* %10.1fc

merge m:1 ccode year using "Vincent&Mango_DataFile1.dta"
drop _merge

save "Vincent&Mango_DataFile2.dta" , replace



